
             
/**
 * 
 *
 * @File name:  TPersonnalDao.java 
 * @Create on:  2011-10-30 23:38:965
 * @Author   :  陈伟
 *
 * @ChangeList
 * ---------------------------------------------------
 * Date         Editor              ChangeReasons
 *
 */
           
package com.boho.estates.staff.dao;

            
import java.sql.Connection;
import java.util.LinkedList;
import java.util.List;

import cn.boho.framework.po.CQuery;
import cn.boho.framework.po.CQueryFactoryTool;
import cn.boho.framework.po.ComboPager;
import cn.boho.framework.po.DAO;
import cn.boho.framework.po.DynaBeanMap;
import cn.boho.framework.po.Pager;
            
        /**
 * 【人员信息:T_PERSONNAL】的Dao操作类
 * 
 */
public class TPersonnalDao extends DAO {
	/**
	* 查询所有【人员信息:T_PERSONNAL】信息
	* @param con
	* @return
	* @throws Exception
	* @date 2011-10-30
	* @author 陈伟
	*
	*/
	public static List queryTPersonnal(Connection con, String departIds)throws Exception{
		List list = new LinkedList();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from T_PERSONNAL where IS_VALID = 1");
		sql.append(" and DEPARTMENT_NO in (").append(departIds).append(")");
		sql.append(" order by PERSONNAL_NO desc");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setCommand(sql.toString());
		list = query.getDynaBeanMapList("T_PERSONNAL", con);
		return list;
	}
	
	public static List queryDistributePersonList(Connection con) throws Exception{
		List list = new LinkedList();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from T_PERSONNAL where ifnull(SYS_USER_ID,0) = 0 and IS_VALID = 1 order by PERSONNAL_NO");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setCommand(sql.toString());
		list = query.getDynaBeanMapList("T_PERSONNAL", con);
		return list;
	}
	
	
	
	/**
	* 分页查询所有【人员信息:T_PERSONNAL】信息
	* @param con
	* @param pager
	* @return
	* @throws Exception
	* @date 2011-10-30
	* @author 陈伟
	*
	*/
	public static ComboPager queryTPersonnalByPager(Connection con,Pager pager, String name, Integer departmentNo, String departIds)throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select * from T_PERSONNAL where IS_VALID = 1");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		int i = 0;
		if(!"".equals(name)){
			sql.append(" and lower(NAME) like ?");
			query.setString(++i,"%"+name.toLowerCase()+"%");
		}
		if(departmentNo != -1){
			sql.append(" and DEPARTMENT_NO = ?");
			query.setInt(++i, departmentNo);
		}
		sql.append(" and DEPARTMENT_NO in (").append(departIds).append(")");
		query.setCommand(sql.toString());
		logger.debug("sql>>>>>>>>>>" + query.getCommand());
		return query.getDynaBeanMapComboPager("T_PERSONNAL", pager,con);
	}
	/**
	* 根据主键【人员编号:PERSONNAL_NO|】获得信息
	* @param con
	* @param personnalNo 人员编号
	* @return
	* @throws Exception
	* @date 2011-10-30
	* @author 陈伟
	*
	*/
	public static DynaBeanMap getTPersonnalByPk(Connection con,java.lang.Integer personnalNo)throws Exception{
		DynaBeanMap dynaBeanMap = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select * from T_PERSONNAL where  PERSONNAL_NO=? and IS_VALID = 1");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setCommand(sql.toString());
		query.setInt(1,personnalNo);
		dynaBeanMap = query.getDynaBeanMap("T_PERSONNAL", con);
		return dynaBeanMap;
	}
	
	public static DynaBeanMap getTPersonnalByUserID(Connection con,java.lang.Integer userID)throws Exception{
		DynaBeanMap dynaBeanMap = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select * from T_PERSONNAL where  SYS_USER_ID=? and IS_VALID = 1");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setCommand(sql.toString());
		query.setInt(1,userID);
		dynaBeanMap = query.getDynaBeanMap("T_PERSONNAL", con);
		return dynaBeanMap;
	}
	
	
	public static List queryBelongChildDepartmentList(Connection con, String departmentIds) throws Exception{
		List list = new LinkedList();
		StringBuffer sql = new StringBuffer();
		sql.append("select b.DEPARTMENT_NO,b.DEPARTMENT_NAME,b.DEPARTMENT_PNO ");
		sql.append("from t_department a, t_department b where ");
		sql.append("a.DEPARTMENT_NO=b.DEPARTMENT_PNO and ");
		sql.append("(a.DEPARTMENT_NO in (").append(departmentIds).append(") or a.DEPARTMENT_PNO in (").append(departmentIds).append(")");
		sql.append("and a.IS_VALID = 1 and b.IS_VALID = 1 ");
//		sql.append("union select DEPARTMENT_NO,DEPARTMENT_NAME,DEPARTMENT_PNO ");
//		sql.append("from t_department where DEPARTMENT_NO=? and IS_VALID = 1 ");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setCommand(sql.toString());
//		query.setInt(1, departmentId);
//		query.setInt(2, departmentId);
//		query.setInt(3, departmentId);
		list = query.getDynaBeanMapList("T_PERSONNAL", con);
		return list;
	}
	
	
	
	
	public static List queryBelongDepartmentsList(Connection con, Integer userId) throws Exception{
		List list = new LinkedList();
		StringBuffer sql = new StringBuffer();
		sql.append("select b.DEPARTMENT_NO,b.DEPARTMENT_NAME,b.DEPARTMENT_PNO ");
		sql.append("from t_department a, t_department b where ");
		sql.append("a.DEPARTMENT_NO=b.DEPARTMENT_PNO and ");
		sql.append("(b.DEPARTMENT_NO in (select DEPARTMENT_NO from t_user_department where USER_ID = ?)");
		sql.append(" or b.DEPARTMENT_PNO in (select DEPARTMENT_NO from t_user_department where USER_ID = ?))");
		sql.append(" and a.IS_VALID = 1 and b.IS_VALID = 1 ");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setCommand(sql.toString());
		System.out.println(sql.toString());
		query.setInt(1, userId);
		query.setInt(2, userId);
		list = query.getDynaBeanMapList("T_PERSONNAL", con);
		return list;
	}
	
	/*
	public static List queryBelongDepartmentsList(Connection con, Integer userId) throws Exception{
		List list = new LinkedList();
		StringBuffer sql = new StringBuffer();
		sql.append("select DEPARTMENT_NO from t_user_department where USER_ID = ?");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setCommand(sql.toString());
		query.setInt(1, userId);
		list = query.getDynaBeanMapList("T_PERSONNAL", con);
		return list;
	}
	*/
	public static List getAdminDepartmentList(Connection con) throws Exception{
		List list = new LinkedList();
		StringBuffer sql = new StringBuffer();
		sql.append("select DEPARTMENT_NO from t_department where IS_VALID = 1");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setCommand(sql.toString());
		list = query.getDynaBeanMapList("T_PERSONNAL", con);
		return list;
	}
	
	public static List queryDistributePersonListForUpdate(Connection con, Integer userid) throws Exception{
		List list = new LinkedList();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from T_PERSONNAL where ifnull(SYS_USER_ID,0) = 0 and IS_VALID = 1 order by PERSONNAL_NO");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setCommand(sql.toString());
		list = query.getDynaBeanMapList("T_PERSONNAL", con);
		return list;
	}
	
}
